<?php
/**
 * (logic) 解析费用（通用）
 */
namespace App\Logic\MonthlyReport\ParseTmp;

use App\Models\Basics\AmazonAccount;
use App\Models\Basics\AmazonCountry;
use App\Models\Basics\SysCountry;
use App\Models\Basics\SysArea;
use App\Models\Basics\SysICountry;
use App\Models\Basics\SysPlatAccount;
use App\Models\MonthlyReport\Tmp\TmpPCPlanFee;
use App\Models\Product\ProductAmazonSku;
use App\Models\MonthlyReport\Payment\CommonFee;
use App\Models\MonthlyReport\Payment\InventoryEvent;
use App\Models\MonthlyReport\Payment\LongTermStorage;
use App\Models\MonthlyReport\Payment\MonthlyStorage;
use App\Models\MonthlyReport\Payment\MRVat;
use App\Models\MonthlyReport\Cost\LogisticsBasics;
use App\Models\MonthlyReportCommon\Cost\PriceBasics;
use App\Models\MonthlyReport\Payment\RemovalOrder;
use App\Models\MonthlyReport\Tmp\TmpAds;
use App\Models\MonthlyReport\Tmp\TmpPCoFee;
use App\Models\MonthlyReport\Tmp\TmpPIcFee;
use App\Models\MonthlyReport\Tmp\TmpPReFee;
use App\Models\MonthlyReport\Tmp\TmpPRmFee;
use App\Models\MonthlyReport\Tmp\TmpPSales;
use App\Models\MonthlyReport\Tmp\TmpPThFee;
use App\Models\MonthlyReport\Tmp\TmpPTotal;
use App\Models\Reimburse\ReimburseDetail;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
class ParseCommonFee
{
    public static function exeParse($date){
        ini_set('memory_limit', '200M');
        set_time_limit(0);
        $country = AmazonCountry::options()->toArray();
        $countryOption = array_flip($country);
        $account = SysPlatAccount::options()->toArray();
        $accountOption = array_flip($account);
        self::exeInventoryCompensation($accountOption,$countryOption,$date);//库存补偿费 汇总到账号+国家
        sleep(2);
        self::exeCommission($accountOption,$countryOption,$date);//佣金 汇总到账号+国家
        sleep(2);
        self::exeVolume($accountOption,$countryOption,$date);//volume 汇总到账号+国家
        sleep(2);
        self::exeRefund($accountOption,$countryOption,$date);//refund 汇总到账号+国家
        sleep(2);
        self::exeThFBA($accountOption,$countryOption,$date);//第三方FBA发货费 汇总到账号+国家
        sleep(2);
        self::exeConsolidation($accountOption,$countryOption,$date);//并仓费 月仓储费 汇总到账号+国家
        sleep(2);
        self::exeLongTermStorage($accountOption,$countryOption,$date);//长期库存费用 汇总到账号+国家
        sleep(2);
        self::exeRemoval($accountOption,$countryOption,$date);//移除费 汇总到账号+国家
        sleep(2);
        self::exePromotion($accountOption,$countryOption,$date);//促销费 汇总到账号+国家
        sleep(2);
        self::exeAds($accountOption,$countryOption,$date);//广告费 汇总到账号+国家
        sleep(2);
        self::exeService($date);//运营服务费 汇总到国家
        sleep(2);
        self::exeReturnSales($date);//退货二次销售 汇总到站点   price
        sleep(2);
        self::exeReturnSalesNotEU($country,$date);//退货二次销售 汇总到国家   price
        sleep(2);
        self::_getRODestructionEU($date);//货损费用 汇总到站点   price
        sleep(2);
        self::exeVAT($date);//VAT费 汇总到国家
        sleep(2);
        self::exeCPlan($accountOption,$countryOption,$date);//清库计划费 汇总到账号+国家
        sleep(2);
        self::exeTotal($accountOption,$countryOption,$date);//Total 汇总到账号+国家
    }

    private static function _replaceB2B($str){
        if(Str::endsWith($str,'B2B')){
            if(Str::endsWith($str,'-B2B')){
                $pos = strripos($str,'-B2B');
            }else{
                $pos = strripos($str,'B2B');
            }
            $str = substr($str,0,$pos);
        }
        return $str;
    }

    private static function _conversionB2B(Collection $collection){
        $i = 0;
        $num = 100;
        $lines = [];
        while(true){
            if($collection->isEmpty())break;
            $items = $collection->splice($i,$num);
            foreach ($items as $item){
                $_account = self::_replaceB2B($item->account);
                if(!array_key_exists($_account,$lines)){
                    $lines[$_account] = $item->total;
                }else{
                    $lines[$_account] += $item->total;
                }
            }
        }
        return $lines;
    }

    private static function _getAccount($account,$country,$str){
        $pos = strripos($str,'-');
        if(!$pos){
            return [0,0,0];
        }
        $prefix = substr($str,0,$pos);
        $countryStr = substr($str,$pos+1);
        $prefix = str_replace('ZH', '账号', $prefix);
        if($countryStr == 'EU'){
            $accountId = array_key_exists($str,$account) ? $account[$str] : 0;
            $countryId = 0;
            $areaId = SysArea::getSysAreaIdByArea('EU');
        }else{
            if(in_array($countryStr,SysArea::$_NACountryForAccount)){
                $suffix = 'NA';
            }elseif(in_array($countryStr,SysArea::$_EUCountryForAccount)){
                $suffix = 'EU';
            }else{
                $suffix = $countryStr;
            }

            $str = $prefix.'-'.$suffix;
            $accountId = array_key_exists($str,$account) ? $account[$str] : 0;
            $countryId = array_key_exists($countryStr,$country) ? $country[$countryStr] : 0;
            $areaId = SysICountry::getAreaIdByCountryId($countryId);
        }
        return [$accountId,$areaId,$countryId];
    }

    //库存补偿费
    public static function exeInventoryCompensation($account,$country,$date){
        $fees = TmpPIcFee::select(DB::raw('account,sum(total) as ic_fee'))
            ->whereNull('sku')
            ->where('report_date',$date)
            ->groupBy('account')
            ->pluck('ic_fee','account');
        $fee = [];
        foreach ($fees as $_account => $_fee){
            $_account = self::_replaceB2B($_account);
            if(!array_key_exists($_account,$fee)){
                $fee[$_account] = $_fee;
            }else{
                $fee[$_account] += $_fee;
            }
        }
        $lines = [];
        $exist = [];
        TmpPIcFee::where('report_date',$date)
            ->whereNotNull('sku')
            ->select('account','country','sku','total')
            ->orderBy('id')
            ->chunk(500, function ($items) use (&$lines,&$exist,$country){
                foreach ($items as $item){
                    $countryId = array_key_exists($item->country,$country) ? $country[$item->country] : 0;
                    $key = $countryId.'_'.$item->sku;
                    if(!array_key_exists($key,$exist)){
                        $count = ProductAmazonSku::query()
                            ->where('country_id',$countryId)
                            ->where('seller_sku',$item->sku)
                            ->count();
                        $exist[$key] = $count > 0 ? true : false;
                    }
                    if(!$exist[$key]){
                        $_account = self::_replaceB2B($item->account);
                        if(!array_key_exists($_account,$lines)){
                            $lines[$_account] = $item->total;
                        }else{
                            $lines[$_account] += $item->total;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $_accounts = array_unique(array_merge(array_keys($fee),array_keys($lines)));
        foreach ($_accounts as $_account){
            $info = self::_getAccount($account,$country,$_account);
            $icFee = ($fee[$_account] ?? 0) + ($lines[$_account] ?? 0);
            CommonFee::updateOrCreate(['report_date' => $date,
                'account_id' => $info[0],
                'area_id' => $info[1],
                'country_id' => $info[2],
                'seller_sku' => 0],
                ['ic_fee' => $icFee]);
        }
    }

    //佣金
    public static function exeCommission($account,$country,$date){
        $fee = TmpPSales::query()->select(DB::raw('account,sum(other) as total'))
            ->where('type','Service Fee')
            ->where('report_date',$date)
            ->groupBy('account')
            ->get();
        $lines = self::_conversionB2B($fee);
        $i = 0;
        $num = 100;
        while(true){
            if(empty($lines))break;
            $_lines = array_splice($lines,$i,$num);
            foreach ($_lines as $_account => $line){
                $info = self::_getAccount($account,$country,$_account);
                CommonFee::updateOrCreate(['report_date' => $date,
                    'account_id' => $info[0],
                    'area_id' => $info[1],
                    'country_id' => $info[2],
                    'seller_sku' => 0],
                    ['selling_fees' => $line]);
            }
        }
    }

    //volume
    public static function exeVolume($account,$country,$date){
        $fee = TmpPSales::select(DB::raw('account,sum(quantity) as quantity,sum(IFNULL(product_sales,0) + IFNULL(shipping_credits,0) + IFNULL(gift_wrap_credits,0) + IFNULL(other,0)) as sales'))
            ->where('report_date',$date)
            ->whereIn('type',['Order','Liquidations','Liquidations Adjustments'])
            ->whereNull('sku')
            ->groupBy('account')
            ->get();
        $i = 0;
        $num = 100;
        $lines = [];
        while(true){
            if($fee->isEmpty())break;
            $items = $fee->splice($i,$num);
            foreach ($items as $item){
                $_account = self::_replaceB2B($item->account);
                if(!array_key_exists($_account,$lines)){
                    $lines[$_account] = ['quantity' => $item->quantity,'sales' => $item->sales];
                }else{
                    $lines[$_account]['quantity'] += $item->quantity;
                    $lines[$_account]['sales'] += $item->sales;
                }
            }
        }
        $i = 0;
        $num = 100;
        while(true){
            if(empty($lines))break;
            $_lines = array_splice($lines,$i,$num);
            foreach ($_lines as $_account => $line){
                $info = self::_getAccount($account,$country,$_account);
                CommonFee::updateOrCreate(['report_date' => $date,
                    'account_id' => $info[0],
                    'area_id' => $info[1],
                    'country_id' => $info[2],
                    'seller_sku' => 0],
                    ['sales_volume' => $line['quantity'],'sales_amount' => $line['sales']]);
            }
        }
    }

    //refund
    public static function exeRefund($account,$country,$date){
        $fee = TmpPReFee::select(DB::raw('account,sum(IFNULL(total,0)) as total'))
            ->whereNull('sku')
            ->where('report_date',$date)
            ->groupBy('account')
            ->get();
        $lines = self::_conversionB2B($fee);
        $s = TmpPReFee::select(DB::raw('account,sum(IFNULL(quantity,0)) as quantity'))
            ->where('report_date',$date)
            ->where('product_sales','<>',0)
            ->whereNull('sku')
            ->groupBy('account')
            ->get();
        $data = [];
        foreach ($s as $_s){
            $_account = self::_replaceB2B($_s->account);
            if(!array_key_exists($_account,$data)){
                $data[$_account] = $_s->quantity;
            }else{
                $data[$_account] += $_s->quantity;
            }
        }
        $i = 0;
        $num = 100;
        while(true){
            if(empty($lines))break;
            $_lines = array_splice($lines,$i,$num);
            foreach ($_lines as $_account => $line){
                $info = self::_getAccount($account,$country,$_account);
                if(array_key_exists($_account,$data)){
                    $quantity = $data[$_account];
                }else{
                    $quantity = 0;
                }
                CommonFee::updateOrCreate(['report_date' => $date,
                    'account_id' => $info[0],
                    'area_id' => $info[1],
                    'country_id' => $info[2],
                    'seller_sku' => 0],
                    ['refund_num' => $quantity,'refund_amount' => $line]);
            }
        }
    }

    //第三方FBA发货费
    public static function exeThFBA($account,$country,$date){
        $fees = TmpPThFee::select(DB::raw('account,sum(total) as thfba'))
            ->whereNull('sku')
            ->where('report_date',$date)
            ->groupBy('account')
            ->pluck('thfba','account');
        $fee = [];
        foreach ($fees as $_account => $_fee){
            $_account = self::_replaceB2B($_account);
            if(!array_key_exists($_account,$fee)){
                $fee[$_account] = $_fee;
            }else{
                $fee[$_account] += $_fee;
            }
        }
        $lines = [];
        $exist = [];
        TmpPThFee::where('report_date',$date)
            ->whereNotNull('sku')
            ->select('account','country','sku','total')
            ->orderBy('id')
            ->chunk(500, function ($items) use (&$lines,&$exist,$country){
                foreach ($items as $item){
                    $countryId = array_key_exists($item->country,$country) ? $country[$item->country] : 0;
                    $key = $countryId.'_'.$item->sku;
                    if(!array_key_exists($key,$exist)){
                        $count = ProductAmazonSku::where('country_id',$countryId)
                            ->where('seller_sku',$item->sku)
                            ->count();
                        $exist[$key] = $count > 0 ? true : false;
                    }
                    if(!$exist[$key]){
                        $_account = self::_replaceB2B($item->account);
                        if(!array_key_exists($_account,$lines)){
                            $lines[$_account] = $item->total;
                        }else{
                            $lines[$_account] += $item->total;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $_accounts = array_unique(array_merge(array_keys($fee),array_keys($lines)));
        foreach ($_accounts as $_account){
            $info = self::_getAccount($account,$country,$_account);
            $thfba = ($fee[$_account] ?? 0) + ($lines[$_account] ?? 0);
            CommonFee::updateOrCreate(['report_date' => $date,
                'account_id' => $info[0],
                'area_id' => $info[1],
                'country_id' => $info[2],
                'seller_sku' => 0],
                ['thfba' => $thfba]);
        }
    }

    //并仓费 月仓储费
    public static function exeConsolidation($account,$country,$date){
        $fees = TmpPCoFee::select(DB::raw('account,sum(other) as ms_fee'))
            ->where('report_date',$date)
            ->groupBy('account')
            ->pluck('ms_fee','account')
            ->toArray();
        $fee = [];
        foreach ($fees as $_account => $_fee){
            $_account = self::_replaceB2B($_account);
            if(!array_key_exists($_account,$fee)){
                $fee[$_account] = $_fee;
            }else{
                $fee[$_account] += $_fee;
            }
        }
        $lines = [];
        $exist = [];
        MonthlyStorage::where('report_date',$date)
            ->select('account','country_id','asin','fnsku','estimated_monthly_storage_fee')
            ->orderBy('id')
            ->chunk(2000, function ($items) use (&$lines,&$exist,$account,$country){
                foreach ($items as $item){
                    $_account = self::_replaceB2B($item->account);
                    $info = self::_getAccount($account,$country,$_account);
                    $key = $info[0].'_'.$item->country_id.'_'.$item->asin.'_'.$item->fnsku;
                    if(!array_key_exists($key,$exist)){
                        $count = ProductAmazonSku::where('country_id',$item->country_id)
                            ->where('account_id',$info[0])
                            ->where('child_asin',$item->asin)
                            ->where('fnsku',$item->fnsku)
                            ->count();
                        $exist[$key] = $count > 0 ? true : false;
                    }
                    if(!$exist[$key]){
                        if(!array_key_exists($_account,$lines)){
                            $lines[$_account] = $item->estimated_monthly_storage_fee;
                        }else{
                            $lines[$_account] += $item->estimated_monthly_storage_fee;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $_accounts = array_unique(array_merge(array_keys($fee),array_keys($lines)));
        foreach ($_accounts as $_account){
            $info = self::_getAccount($account,$country,$_account);
            $msFee = abs($fee[$_account] ?? 0) + ($lines[$_account] ?? 0);
            CommonFee::updateOrCreate(['report_date' => $date,
                'account_id' => $info[0],
                'area_id' => $info[1],
                'country_id' => $info[2],
                'seller_sku' => 0],
                ['ms_fee' => $msFee]);
        }
    }

    //移除费
    public static function exeRemoval($account,$country,$date){
        $fees = TmpPRmFee::select(DB::raw('account,sum(other) as rm_fee'))
            ->where('report_date',$date)
            ->groupBy('account')
            ->pluck('rm_fee','account');
        $fee = [];
        foreach ($fees as $_account => $_fee){
            $_account = self::_replaceB2B($_account);
            if(!array_key_exists($_account,$fee)){
                $fee[$_account] = $_fee;
            }else{
                $fee[$_account] += $_fee;
            }
        }
        $lines = [];
        $exist = [];
        RemovalOrder::where('report_date',$date)
            ->select('account','country_id','sku','removal_fee')
            ->where('country_id','<>',SysCountry::EU_COUNTRY_ID)
            ->orderBy('id')
            ->chunk(200, function ($items) use (&$lines,&$exist){
                foreach ($items as $item){
                    $key = $item->country_id.'_'.$item->sku;
                    if(!array_key_exists($key,$exist)){
                        $count = ProductAmazonSku::where('country_id',$item->country_id)
                            ->where('seller_sku',$item->sku)
                            ->count();
                        $exist[$key] = $count > 0 ? true : false;
                    }
                    if(!$exist[$key]){
                        $_account = self::_replaceB2B($item->account);
                        if(!array_key_exists($_account,$lines)){
                            $lines[$_account] = $item->removal_fee;
                        }else{
                            $lines[$_account] += $item->removal_fee;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $_accounts = array_unique(array_merge(array_keys($fee),array_keys($lines)));
        foreach ($_accounts as $_account){
            $info = self::_getAccount($account,$country,$_account);
            $rmFee = ($fee[$_account] ?? 0) + ($lines[$_account] ?? 0);
            CommonFee::updateOrCreate(['report_date' => $date,
                'account_id' => $info[0],
                'area_id' => $info[1],
                'country_id' => $info[2],
                'seller_sku' => 0],
                ['rm_fee' => $rmFee]);
        }
    }

    //促销费
    public static function exePromotion($account,$country,$date){
        $fee = TmpPSales::select(DB::raw('account,sum(IFNULL(promotional_rebates,0) + IFNULL(other_transaction_fees,0)) as rebates,sum(IFNULL(fba_fees,0)) as fba_fees'))
            ->whereNull('sku')
            ->where('report_date',$date)
            ->groupBy('account')
            ->get();
        $i = 0;
        $num = 100;
        $lines = [];
        while(true){
            if($fee->isEmpty())break;
            $items = $fee->splice($i,$num);
            foreach ($items as $item){
                $_account = self::_replaceB2B($item->account);
                if(!array_key_exists($_account,$lines)){
                    $lines[$_account] = ['rebates' => $item->rebates,'fba_fees' => $item->fba_fees];
                }else{
                    $lines[$_account]['rebates'] += $item->rebates;
                    $lines[$_account]['fba_fees'] += $item->fba_fees;
                }
            }
        }
        $i = 0;
        $num = 100;
        while(true){
            if(empty($lines))break;
            $_lines = array_splice($lines,$i,$num);
            foreach ($_lines as $_account => $line){
                $info = self::_getAccount($account,$country,$_account);
                CommonFee::updateOrCreate(['report_date' => $date,
                    'account_id' => $info[0],
                    'area_id' => $info[1],
                    'country_id' => $info[2],
                    'seller_sku' => 0],
                    ['rebates' => $line['rebates'],'fba_fees' => $line['fba_fees']]);
            }
        }
    }

    //长期库存费用
    public static function exeLongTermStorage($account,$country,$date){
        $lines = [];
        $exist = [];
        LongTermStorage::where('report_date',$date)
            ->select('account','country_id','sku','storage_fee')
            ->orderBy('id')
            ->chunk(100, function ($items) use (&$lines,&$exist){
                foreach ($items as $item){
                    $key = $item->country_id.'_'.$item->sku;
                    if(!array_key_exists($key,$exist)){
                        $count = ProductAmazonSku::where('country_id',$item->country_id)
                            ->where('seller_sku',$item->sku)
                            ->count();
                        $exist[$key] = $count > 0 ? true : false;
                    }
                    if(!$exist[$key]){
                        $_account = self::_replaceB2B($item->account);
                        if(!array_key_exists($_account,$lines)){
                            $lines[$_account] = $item->storage_fee;
                        }else{
                            $lines[$_account] += $item->storage_fee;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $i = 0;
        $num = 100;
        while(true){
            if(empty($lines))break;
            $_lines = array_splice($lines,$i,$num);
            foreach ($_lines as $_account => $fee){
                $info = self::_getAccount($account,$country,$_account);
                CommonFee::updateOrCreate(['report_date' => $date,
                    'account_id' => $info[0],
                    'area_id' => $info[1],
                    'country_id' => $info[2],
                    'seller_sku' => 0],
                    ['lts_fee' => $fee]);
            }
        }
    }

    //广告费
    public static function exeAds($account,$country,$date){
        $fees = TmpAds::select(DB::raw('account,sum(spend) as spend'))
            ->whereNull('seller_sku')
            ->where('report_date',$date)
            ->groupBy('account')
            ->pluck('spend','account');
        $fee = [];
        foreach ($fees as $_account => $_fee){
            $_account = self::_replaceB2B($_account);
            if(!array_key_exists($_account,$fee)){
                $fee[$_account] = $_fee;
            }else{
                $fee[$_account] += $_fee;
            }
        }
        $lines = [];
        $exist = [];
        TmpAds::where('report_date',$date)
            ->whereNotNull('seller_sku')
            ->select('account','country','seller_sku as sku','spend')
            ->orderBy('id')
            ->chunk(500, function ($items) use (&$lines,&$exist,$country){
                foreach ($items as $item){
                    $countryId = array_key_exists($item->country,$country) ? $country[$item->country] : 0;
                    $key = $countryId.'_'.$item->sku;
                    if(!array_key_exists($key,$exist)){
                        $count = ProductAmazonSku::where('country_id',$countryId)
                            ->where('seller_sku',$item->sku)
                            ->count();
                        $exist[$key] = $count > 0 ? true : false;
                    }
                    if(!$exist[$key]){
                        $_account = self::_replaceB2B($item->account);
                        if(!array_key_exists($_account,$lines)){
                            $lines[$_account] = $item->spend;
                        }else{
                            $lines[$_account] += $item->spend;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $_accounts = array_unique(array_merge(array_keys($fee),array_keys($lines)));
        foreach ($_accounts as $_account){
            $info = self::_getAccount($account,$country,$_account);
            //UK不需要扣税了
//            $spend = (($fee[$_account] ?? 0) + ($lines[$_account] ?? 0)) * ($info[2] == 3 ? 1.2 : 1);
            $spend = ($fee[$_account] ?? 0) + ($lines[$_account] ?? 0);
            CommonFee::updateOrCreate(['report_date' => $date,
                'account_id' => $info[0],
                'area_id' => $info[1],
                'country_id' => $info[2],
                'seller_sku' => 0],
                ['spend' => $spend]);
        }
    }

    //运营服务费
    public static function exeService($date){
        $rd = ReimburseDetail::where('fee_type','<>',1)
            ->where('paid_status',3)
            ->where('report_date',$date)
            ->whereNull('seller_sku')
            ->select(DB::raw('country_id,sum(amount_rmb) as reimburse_os_rmb'))
            ->groupBy('country_id')
            ->get();
        $area = [];
        foreach ($rd as $_rd){
            if(!array_key_exists($_rd->country_id,$area)){
                $area[$_rd->country_id] = SysICountry::getAreaIdByCountryId($_rd->country_id);
            }
            CommonFee::updateOrCreate(['report_date' => $date,
                'account_id' => 0,
                'area_id' => $area[$_rd->country_id],
                'country_id' => $_rd->country_id,
                'seller_sku' => 0],
                ['reimburse_os_rmb' => $_rd->reimburse_os_rmb]);
        }
    }

    //退货二次销售-EU
    public static function exeReturnSales($date){
        $lines = [];
        InventoryEvent::select('sku','quantity')
            ->where('report_date',$date)
            ->where('country_id',SysCountry::EU_COUNTRY_ID)
            ->orderBy('id')
            ->chunk(200, function ($items) use (&$lines){
                foreach ($items as $item){
                    if(!array_key_exists($item->sku,$lines)){
                        $lines[$item->sku] = $item->quantity;
                    }else{
                        $lines[$item->sku] += $item->quantity;
                    }
                }
            });
        $areaId = SysArea::getSysAreaIdByArea('EU');
        $countryIdsEU = SysICountry::getCountryIdsByAreaId($areaId);
        $areaIdUK = SysArea::getSysAreaIdByArea('UK');
        $countryIdsUK = SysICountry::getCountryIdsByAreaId($areaIdUK);
        $countryIds = [];
        foreach ($countryIdsEU as $countryIdEU){
            if(!in_array($countryIdEU,$countryIds)){
                $countryIds[] = $countryIdEU;
            }
        }
        foreach ($countryIdsUK as $countryIdUK){
            if(!in_array($countryIdUK,$countryIds)){
                $countryIds[] = $countryIdUK;
            }
        }
        $i = 0;
        $num = 200;
        while(true){
            if(empty($lines))break;
            $_lines = array_splice($lines,$i,$num);
            foreach ($_lines as $sku => $quantity){
                $fee = self::_getReturnSales([$areaId,$areaIdUK],$countryIds,$sku,$quantity,$date);
                CommonFee::updateOrCreate(['report_date' => $date,
                    'account_id' => 0,
                    'area_id' => $areaId,
                    'country_id' => 0,
                    'seller_sku' => $sku],
                    ['ied_rmb' => $fee]);
            }
        }
    }

    //退货二次销售-非EU
    public static function exeReturnSalesNotEU($country,$date){
        $lines = [];
        $exist = [];
        InventoryEvent::select('country_id','sku','quantity')
            ->where('report_date',$date)
            ->where('country_id','<>',SysCountry::EU_COUNTRY_ID)
            ->orderBy('id')
            ->chunk(200, function ($items) use (&$lines,&$exist){
                foreach ($items as $item){
                    $key = $item->country_id.'_'.$item->sku;
                    if(!array_key_exists($key,$exist)){
                        $count = ProductAmazonSku::where('country_id',$item->country_id)
                            ->where('seller_sku',$item->sku)
                            ->count();
                        $exist[$key] = $count > 0 ? true : false;
                    }
                    if(!$exist[$key]){
                        if(!array_key_exists($key,$lines)){
                            $lines[$key] = $item->quantity;
                        }else{
                            $lines[$key] += $item->quantity;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $i = 0;
        $num = 200;
        while(true){
            if(empty($lines))break;
            $_lines = array_splice($lines,$i,$num);
            foreach ($_lines as $lineKey => $quantity){
                list($countryId,$sku) = explode('_',$lineKey);
                $info = self::_getReturnSalesNotEU($country,$countryId,$sku,$quantity,$date);
                CommonFee::updateOrCreate(['report_date' => $date,
                    'account_id' => 0,
                    'area_id' => $info[0],
                    'country_id' => $info[1],
                    'seller_sku' => $sku],
                    ['ied_rmb' => $info[2]]);
            }
        }
    }

    //VAT
    public static function exeVAT($date){
        $area = [];
        MRVat::select('country_id','amount')
            ->where('report_date',$date)
            ->orderBy('id')
            ->chunk(50, function ($items) use(&$area,$date) {
                foreach ($items as $item){
                    if(!array_key_exists($item->country_id,$area)){
                        if($item->country_id == SysCountry::EU_COUNTRY_ID){
                            $area[$item->country_id]['area'] = SysArea::getSysAreaIdByArea('EU');
                            $area[$item->country_id]['country'] = 0;
                        }else{
                            $area[$item->country_id]['area'] = SysICountry::getAreaIdByCountryId($item->country_id);
                            $area[$item->country_id]['country'] = $item->country_id;
                        }
                    }
                    CommonFee::updateOrCreate(['report_date' => $date,
                        'account_id' => 0,
                        'area_id' => $area[$item->country_id]['area'],
                        'country_id' => $area[$item->country_id]['country']],
                        ['vat_fee' => $item->amount]);
                }
            });
    }

    //清库计划费用
    public static function exeCPlan($account,$country,$date){
        $fees = TmpPCPlanFee::select(DB::raw('account,sum(total) as total'))
            ->whereNull('sku')
            ->where('report_date',$date)
            ->groupBy('account')
            ->pluck('total','account')
            ->toArray();
        $fee = [];
        foreach ($fees as $_account => $_fee){
            $_account = self::_replaceB2B($_account);
            if(!array_key_exists($_account,$fee)){
                $fee[$_account] = $_fee;
            }else{
                $fee[$_account] += $_fee;
            }
        }
        $lines = [];
        $exist = [];
        TmpPCPlanFee::where('report_date',$date)
            ->whereNotNull('sku')
            ->select('account','country','sku','total')
            ->orderBy('id')
            ->chunk(2000, function ($items) use (&$lines,&$exist,$country){
                foreach ($items as $item){
                    $countryId = array_key_exists($item->country,$country) ? $country[$item->country] : 0;
                    $key = $countryId.'_'.$item->sku;
                    if(!array_key_exists($key,$exist)){
                        $count = ProductAmazonSku::where('country_id',$countryId)
                            ->where('seller_sku',$item->sku)
                            ->count();
                        $exist[$key] = $count > 0 ? true : false;
                    }
                    if(!$exist[$key]){
                        $_account = self::_replaceB2B($item->account);
                        if(!array_key_exists($_account,$lines)){
                            $lines[$_account] = $item->total;
                        }else{
                            $lines[$_account] += $item->total;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $_accounts = array_unique(array_merge(array_keys($fee),array_keys($lines)));
        foreach ($_accounts as $_account){
            $info = self::_getAccount($account,$country,$_account);
            $total = ($fee[$_account] ?? 0) + ($lines[$_account] ?? 0);
            CommonFee::updateOrCreate(['report_date' => $date,
                'account_id' => $info[0],
                'area_id' => $info[1],
                'country_id' => $info[2],
                'seller_sku' => 0],
                ['cplan_fee' => $total]);
        }
    }

    //Total
    public static function exeTotal($account,$country,$date){
        $fees = TmpPTotal::select(DB::raw('account,sum(total) as total'))
            ->whereNull('sku')
            ->where('report_date',$date)
            ->groupBy('account')
            ->pluck('total','account')
            ->toArray();
        $fee = [];
        foreach ($fees as $_account => $_fee){
            $_account = self::_replaceB2B($_account);
            if(!array_key_exists($_account,$fee)){
                $fee[$_account] = $_fee;
            }else{
                $fee[$_account] += $_fee;
            }
        }
        $lines = [];
        $exist = [];
        TmpPTotal::where('report_date',$date)
            ->whereNotNull('sku')
            ->select('account','country','sku','total')
            ->orderBy('id')
            ->chunk(2000, function ($items) use (&$lines,&$exist,$country){
                foreach ($items as $item){
                    $countryId = array_key_exists($item->country,$country) ? $country[$item->country] : 0;
                    $key = $countryId.'_'.$item->sku;
                    if(!array_key_exists($key,$exist)){
                        $count = ProductAmazonSku::where('country_id',$countryId)
                            ->where('seller_sku',$item->sku)
                            ->count();
                        $exist[$key] = $count > 0 ? true : false;
                    }
                    if(!$exist[$key]){
                        $_account = self::_replaceB2B($item->account);
                        if(!array_key_exists($_account,$lines)){
                            $lines[$_account] = $item->total;
                        }else{
                            $lines[$_account] += $item->total;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $_accounts = array_unique(array_merge(array_keys($fee),array_keys($lines)));
        foreach ($_accounts as $_account){
            $info = self::_getAccount($account,$country,$_account);
            $total = ($fee[$_account] ?? 0) + ($lines[$_account] ?? 0);
            CommonFee::updateOrCreate(['report_date' => $date,
                'account_id' => $info[0],
                'area_id' => $info[1],
                'country_id' => $info[2],
                'seller_sku' => 0],
                ['total' => $total]);
        }
    }

    //销毁货物损失
    private static function _getRODestructionEU($date){
        $lines = [];
        $exist = [];
        RemovalOrder::where('report_date',$date)
            ->select('sku','disposed_quantity')
            ->where('country_id',SysCountry::EU_COUNTRY_ID)
            ->where('disposition','Sellable')
            ->orderBy('id')
            ->chunk(50, function ($items) use (&$lines,&$exist){
                foreach ($items as $item){
                    if(!array_key_exists($item->sku,$exist)){
                        $count = ProductAmazonSku::where('seller_sku',$item->sku)->count();
                        $exist[$item->sku] = $count > 0 ? true : false;
                    }
                    if($exist[$item->sku]){
                        if(!array_key_exists($item->sku,$lines)){
                            $lines[$item->sku] = $item->disposed_quantity;
                        }else{
                            $lines[$item->sku] += $item->disposed_quantity;
                        }
                    }else{
                        continue;
                    }
                }
            });
        $areaId = SysArea::getSysAreaIdByArea('EU');
        $i = 0;
        $num = 10;
        while(true){
            if(empty($lines))break;
            $_lines = array_splice($lines,$i,$num);
            foreach ($_lines as $sku => $quantity){
                $purchasePriceRMB = 0;
                $shipmentPriceRMB = 0;
                $pas = ProductAmazonSku::where('seller_sku',$sku)->orderBy('id')->first();
                if($pas){
                    $purchasePriceRMB = PriceBasics::getPrice($pas->psku_id,$date);
                    $shipmentPriceRMB = LogisticsBasics::getLogistics($areaId,$pas->psku_id,$date);
                }
                $rodRmb = round($quantity * ($purchasePriceRMB + $shipmentPriceRMB),3);
                CommonFee::updateOrCreate(['report_date' => $date,
                    'account_id' => 0,
                    'area_id' => $areaId,
                    'country_id' => 0,
                    'seller_sku' => $sku],
                    ['rod_rmb' => $rodRmb]);
            }
        }
    }

    private static function _getReturnSales($areaIds,$countryIds,$sku,$quantity,$date){
        $pas = ProductAmazonSku::where('seller_sku',$sku)
            ->whereIn('country_id',$countryIds)
            ->orderBy('id')
            ->first();
        if(!$pas)return 0;
        $purchasePriceRmb = PriceBasics::getPrice($pas->psku_id,$date);
        $shipmentPriceRMB = LogisticsBasics::getLogistics($areaIds,$pas->psku_id,$date);
        return round($quantity * ($purchasePriceRmb + $shipmentPriceRMB),3);
    }

    private static function _getReturnSalesNotEU($country,$countryId,$sku,$quantity,$date){
        if(!array_key_exists($countryId,$country))return [0,0,0];
        $pas = ProductAmazonSku::where('seller_sku',$sku)
            ->orderBy('id')
            ->first();
        if(!$pas)return [0,0,0];
        $countryStr = $country[$countryId];
        if(in_array($countryStr,['CA', 'US', 'MX', 'BR'])){
            $countryId = $pas->country_id;
        }
        $areaId = SysICountry::getAreaIdByCountryId($countryId);
        $purchasePriceRmb = PriceBasics::getPrice($pas->psku_id,$date);
        $shipmentPriceRMB = LogisticsBasics::getLogistics($areaId,$pas->psku_id,$date);
        $fee = round($quantity * ($purchasePriceRmb + $shipmentPriceRMB),3);
        return [$areaId,$countryId,$fee];
    }
}
